package com.etrans.web.gps.business.base.controller;

import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSON;
import com.etrans.web.gps.base.controller.BaseController;
import com.etrans.web.gps.business.base.domain.VehicleInfo;
import com.etrans.web.gps.business.base.service.EnterpriseService;
import com.etrans.web.gps.business.base.service.VehicleManagerservice;
import com.etrans.web.gps.business.system.domain.SysUser;
import com.etrans.web.gps.common.Constant;
import com.etrans.web.gps.common.page.PageVo;
import com.etrans.web.gps.util.CatalogExcelUtil;
import com.etrans.web.gps.util.EgovUtil;
import com.etrans.web.gps.util.grid.FlexiGridUtil;

/**
 * 车辆信息管理
 * 
 * * @author grc 2017-08-19
 */
@Controller
@RequestMapping("/base/vehicleManager/*")
public class VehicleManagerController extends BaseController {
	@Autowired
	private VehicleManagerservice vehicleManagerservice;

	@Autowired
	private EnterpriseService enterpriseService;
	
	@Autowired
	private EgovUtil egovUtil;

	private static final Logger logger = LoggerFactory.getLogger(VehicleManagerController.class);

	// 加载车辆信息

	@RequestMapping("loadAllVehicles.html")
	@ResponseBody
	public PageVo queryVehicleList(@RequestParam String paramsGrid) throws Exception {
		PageVo page = new PageVo();
		try {
			Map<String, Object> paramMap = FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam();
			SysUser user = getSysUser();
			if (!user.isSuper_admin()) {
				paramMap.put("roleId", String.format("%08d", user.getArea_id()));
			}
			List<Map<String, Object>> vehicleList = vehicleManagerservice.queryVehicleList(paramMap);
			
			page.setRows(vehicleList);
			page.setPage((int) paramMap.get("page"));
			page.setTotal(vehicleManagerservice.queryVehicleListCount(paramMap));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return page;
	}

	// 车辆的修改和插入
	@RequestMapping(value = "saveVehicle.html")
	@ResponseBody
	public Map<String, Object> saveVehicle(@RequestBody String paramGrid) {
		Map<String, Object> result = new HashMap<>();
		Map<String, Object> params = JSON.parseObject(paramGrid, Map.class);
		SysUser user = getSysUser();
		if (!user.isSuper_admin()) {
			params.put("roleId", String.format("%08d", user.getId()));
		}
		if(!params.containsKey("startTime")){
			params.put("startTime", null);
		}
		if(!params.containsKey("endTime")){
			params.put("endTime", null);
		}
		try {
			String vehicle = params.get("vehicleNo").toString();
			String action = params.get("action").toString();
			if ("insert".equals(action)) {
				int count = vehicleManagerservice.checkIfExistNumber(params);
				int check = vehicleManagerservice.checkIfExistPermitsion(params);
				if (count == 0 && check == 0) {
					String vehicleNo = vehicle.replace(" ", "");
					params.put("vehicleNo", vehicleNo);
					params.put("creator", user.getId());
					Integer enterpriseId = Integer.parseInt(String.valueOf(params.get("enterpriseId")));
					String tradeKinds = enterpriseService.queryEnterprise(enterpriseId);
					boolean flag = false;
					String tradeId = String.valueOf(params.get("tradeId"));
					if (StringUtils.isNotEmpty(tradeKinds)) {
						String[] tradeIds = tradeKinds.split("#");
						for(String str : tradeIds){
							if(str.equals(tradeId)){
								flag =true;
								break;
							}
						}
					}else {
						tradeKinds = "#";
					}
					
					if(!flag){
						tradeKinds =tradeKinds+tradeId+"#";
						Map<String, Object> param = new HashMap<>();
						param.put("tradeKinds", tradeKinds);
						param.put("enterpriseId", enterpriseId);
						enterpriseService.UpdateEnterpriseTradeKinds(param);
					}
					vehicleManagerservice.saveVehicle(params);
					result.put("code", Constant.RETURN_OK_CODE);
					result.put("msg", "保存成功");
				} else if (count != 0) {
					// 返回提示车牌号重复
					result.put("code", Constant.RETURN_Repent_CODE);
					result.put("msg", "存在重复车牌号");
				}else if (check != 0){
					// 返回提示道路运输证号重复
					result.put("code", Constant.RETURN_Repent_CODE);
					result.put("msg", "存在重复道路运输证号");
				}
			} else if ("update".equals(action)) {
				String vehicleNo = vehicle.replace(" ", "");
				params.put("vehicleNo", vehicleNo);
				params.put("updator", user.getId());
				vehicleManagerservice.updateVehicle(params);
				result.put("code", Constant.RETURN_OK_CODE);
				result.put("msg", "保存成功");
			}

		} catch (Exception e) {
			logger.error("", e);
			result.put("code", Constant.RETURN_ERROR_CODE);
			result.put("msg", "保存失败!");
		}
		return result;
	}

	@RequestMapping(value = "initVehicleForm.html")
	@ResponseBody
	public ModelAndView initVehicleForm() {
		ModelAndView mav = new ModelAndView();
		mav.addObject("action", "insert");
		mav.addObject("vehicle", JSON.toJSON(new VehicleInfo()));
		mav.setViewName("views/business/base/vehicle/addVehicleForm");
		return mav;
	}

	// 跳转更新车辆页面
	@RequestMapping(value = "initUpdateVehicleForm.html")
	@ResponseBody
	public ModelAndView initUpdateVehicleForm(Integer vehicleId) {
		ModelAndView mav = new ModelAndView();
		VehicleInfo vehicle = vehicleManagerservice.queryVehicleInfoById(vehicleId);
		mav.addObject("vehicleId", vehicleId);
		mav.addObject("action", "update");
		mav.addObject("vehicle", JSON.toJSON(vehicle));
		mav.setViewName("views/business/base/vehicle/addVehicleForm");
		return mav;
	}

	// 跳转更新车辆页面
	@RequestMapping(value = "viewVehicleDetail.html")
	@ResponseBody
	public ModelAndView viewVehicleDetail(Integer vehicleId) {
		ModelAndView mav = new ModelAndView();
		try {
			VehicleInfo vehicle = vehicleManagerservice.queryVehicleDetail(vehicleId);
			String key = String.format("%s_%s", vehicle.getVehicleNo(), vehicle.getColorId());
			Map<String, List<String>> status = egovUtil.getVehicleStatus(key);
			List<String> onlineList = status.get("online");
			if(!onlineList.isEmpty() && onlineList.size() !=0){
				vehicle.setIsOnline("在线");
			}else{
				vehicle.setIsOnline("离线");
			}
			mav.addObject("vehicleId", vehicleId);
			mav.addObject("action", "detail");
			mav.addObject("vehicle", JSON.toJSON(vehicle));
			mav.setViewName("views/business/base/vehicle/vehicleDetail");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mav;
	}

	// 删除车辆
	@RequestMapping(value = "delVehicle.html")
	@ResponseBody
	public Map<String, Object> batchDeleteVeicle(@RequestParam String[] vehicleIds, String[] vehicleColor) {
		Map<String, Object> result = new HashMap<>();
		HashMap<String, Object> param = new HashMap<>();
		SysUser user = getSysUser();
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String time = dateFormat.format(new Date());
			
			for (int i = 0; i < vehicleIds.length; i++) {
				param.put("vehicleId", vehicleIds[i]);
				param.put("vColor", vehicleColor[i]);
				vehicleManagerservice.deleteVehicleByID(param);
				result.put("code", Constant.RETURN_OK_CODE);
				result.put("msg", "操作成功!");
			}
		} catch (Exception e) {
			logger.error("", e);
			e.printStackTrace();
			result.put("code", Constant.RETURN_ERROR_CODE);
			result.put("msg", "操作失败!");
		}
		return result;
	}

	// 注销车辆
	@RequestMapping(value = "cancelVehicle.html")
	@ResponseBody
	public Map<String, Object> batchCancelVeicle(@RequestParam String[] vehicleIds, String[] vehicleColor) {
		Map<String, Object> result = new HashMap<>();
		HashMap<String, Object> param = new HashMap<>();
		SysUser user = getSysUser();
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String time = dateFormat.format(new Date());
			
			for (int i = 0; i < vehicleIds.length; i++) {
				param.put("vehicleId", vehicleIds[i]);
				param.put("vColor", vehicleColor[i]);
				param.put("updatetime", time);
				param.put("isDeleted", 1);
				param.put("updator", user.getId());
				vehicleManagerservice.cancelVehicleByID(param);
				result.put("code", Constant.RETURN_OK_CODE);
				result.put("msg", "操作成功!");
			}
		} catch (Exception e) {
			logger.error("", e);
			e.printStackTrace();
			result.put("code", Constant.RETURN_ERROR_CODE);
			result.put("msg", "操作失败!");
		}
		return result;
	}
	
	/*
	 * public String getCellValue(HSSFCell cell) { String result = new String();
	 * if (cell == null || "".equals(cell)) { return ""; } switch
	 * (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC:// 鏁板瓧绫诲瀷 if
	 * (HSSFDateUtil.isCellDateFormatted(cell)) {// 澶勭悊鏃ユ湡鏍煎紡銆佹椂闂存牸寮�
	 * SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date
	 * date = cell.getDateCellValue(); result = sdf.format(date); } else {
	 * double value = cell.getNumericCellValue(); CellStyle style =
	 * cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String
	 * temp = style.getDataFormatString(); // 鍗曞厓鏍艰缃垚甯歌 if
	 * (temp.equals("General")) { format.applyPattern("#"); } result =
	 * format.format(value); } break; case HSSFCell.CELL_TYPE_STRING://
	 * String绫诲瀷 result = cell.getRichStringCellValue().toString(); break; case
	 * HSSFCell.CELL_TYPE_BLANK: result = ""; default: result = ""; break; }
	 * return result; }
	 * 
	 */ /**
		 * 导出车辆信息列表
		 * 
		 * @param response
		 * @param paramsGrid
		 * @param StartPage
		 * @param EndPage
		 */
	@SuppressWarnings("unchecked")
	@RequestMapping(value = "exportVehicles.html", method = RequestMethod.POST)
	public void exportUnits(@RequestBody String paramsGrid, HttpServletResponse response) {
		Map<String, Object> paramMap = getReqParameters();
		String colModel = paramMap.get("colModel").toString();
		List<String> colModelList = JSON.parseObject(colModel, List.class);
		SysUser user = getSysUser();
		if (!user.isSuper_admin()) {
			paramMap.put("roleId", String.format("%08d", user.getArea_id()));
		}
		int startPage = Integer.valueOf(String.valueOf(paramMap.get("StartPage")))-1;
		int pageSize = Integer.valueOf(String.valueOf(paramMap.get("pagesize")));
		int endPage = Integer.valueOf(String.valueOf(paramMap.get("EndPage")));
		paramMap.put("fromNumber", startPage*pageSize);
		paramMap.put("pageSize",(endPage-startPage)*pageSize);
		List<Map<String, Object>> list = vehicleManagerservice.queryVehicleList(paramMap);
		
		int index = -1;
		List rowsList = new ArrayList<>();
		rowsList.add(list);
		String title = paramMap.get("title").toString();
		exportExl(request, response, title, removeHeadName(colModelList, index, -1), rowsList, null);
	}

	/*
	 * @RequestMapping(value = "batchUpdateVehicleWorkStatus.html")
	 * 
	 * @ResponseBody public Map<String, Object>
	 * batchUpdateVehicleWorkStatus(@RequestParam String VehicleIds) {
	 * Map<String, Object> result = new HashMap<>(); try { if (VehicleIds !=
	 * null && !"".equals(VehicleIds)) { String[] ArrayvehicleId =
	 * VehicleIds.split(","); for (String perID : ArrayvehicleId) { Integer
	 * vehicleID = Integer.parseInt(perID);
	 * vehicleManagerservice.updateVehicleWorkStatus(vehicleID, request); //
	 * VehicleExpand vehicleExpand = //
	 * vehicleManagerservice.queryVehicleById(vehicleID); result.put("code",
	 * Constant.RETURN_OK_CODE); } } } catch (Exception e) { logger.error("",
	 * e); result.put("code", Constant.RETURN_ERROR_CODE); result.put("code",
	 * "鎿嶄綔澶辫触!"); } return result; }
	 */

	/*
	 * @RequestMapping(value = "initChangeTerminalForm.html")
	 * 
	 * @ResponseBody public ModelAndView initChangeTerminalForm(Integer
	 * vehicleId) { ModelAndView mav = new ModelAndView(); VehicleExpand vehicle
	 * = vehicleManagerservice.queryVehicleById(Integer.valueOf(vehicleId)); //
	 * System.out.println(vehicle.getTerminalId()+"鏂�-------------");
	 * vehicle.setOldTerminalId(vehicle.getTerminalId()); //
	 * System.out.println(vehicle.getOldTerminalId()+"鏃�------------");
	 * mav.addObject("vehicle", vehicle); mav.addObject("vehicleId", vehicleId);
	 * mav.setViewName("views/base/vehicle/changeTerminalForm"); return mav; }
	 * 
	 * @RequestMapping(value = "initTerminalParams.html")
	 * 
	 * @ResponseBody public ModelAndView initTerminalParams(@RequestParam
	 * Integer vehicleId) { ModelAndView mav = new ModelAndView();
	 * mav.addObject("vehicleId", vehicleId);
	 * mav.setViewName("views/base/vehicle/terminalParams"); return mav; }
	 */

	/*
	 * @RequestMapping(value = "initCoverageInfo.html")
	 * 
	 * @ResponseBody public ModelAndView initCoverageInfo(@RequestParam Integer
	 * vehicleId) { ModelAndView mav = new ModelAndView();
	 * mav.addObject("vehicleId", vehicleId);
	 * mav.setViewName("views/base/vehicle/coverageInfo"); return mav; }
	 * 
	 * @RequestMapping(value = "initSignOilPage.html")
	 * 
	 * @ResponseBody public ModelAndView initSignOilPage(@RequestParam String
	 * vehicleIds) { ModelAndView mav = new ModelAndView();
	 * mav.addObject("vehicleIds", vehicleIds);
	 * mav.setViewName("views/base/vehicle/signOil"); return mav; }
	 * 
	 * @RequestMapping(value = "getVehicleInfo.html")
	 * 
	 * @ResponseBody public VehicleExpand getVehicleInfo(@RequestParam String
	 * vehicleId) { Integer id = Integer.parseInt(vehicleId); VehicleExpand
	 * vehicle = vehicleManagerservice.queryVehicleById(id); //
	 * 鍏堜繚瀛樻棫缁堢ID锛屽綋鏇存崲缁堢淇℃伅鏃堕攢鎴�
	 * vehicle.setOldTerminalId(vehicle.getTerminalId()); return vehicle; }
	 * 
	 * // 绲傜杞溅
	 * 
	 * @RequestMapping(value = "changeTerminal.html")
	 * 
	 * @ResponseBody public Map<String, Object> changeTerminal(@RequestParam
	 * HttpServletResponse response, HttpServletRequest request, String action,
	 * VehicleExpand vehicle, Integer id) { Map<String, Object> result = new
	 * HashMap<>(); int Count = 0; try { HashMap<String, Object> param = new
	 * HashMap<String, Object>(); if (vehicle.getTerminalId() != null &&
	 * vehicle.getTerminalId() != 0) {
	 * vehicleManagerservice.changeTerminal_sel(vehicle); // VehicleExpand
	 * vehicleExpand = // vehicleManagerservice.queryVehicleById(id);
	 * result.put("code", Constant.RETURN_OK_CODE); } else { param.put("COMMNO",
	 * vehicle.getNewCommNo()); Count =
	 * terminalService.queryCountByCOMMNO(param); if (Count == 0) {
	 * vehicleManagerservice.changeTerminal_input(request, vehicle); //
	 * VehicleExpand vehicleExpand = //
	 * vehicleManagerservice.queryVehicleById(id); result.put("code",
	 * Constant.RETURN_OK_CODE); } else { result.put("code",
	 * Constant.RETURN_Repent_CODE); } } } catch (Exception e) {
	 * logger.error("", e); result.put("code", Constant.RETURN_ERROR_CODE);
	 * result.put("code", "鎿嶄綔澶辫触!"); } return result; }
	 * 
	 * // 鏍规嵁杞﹁締鏌ョ湅缁堢鍙傛暟
	 * 
	 * @RequestMapping(value = "queryTerminalParamByVehicleID.html")
	 * 
	 * @ResponseBody public PageVo queryTerminalParamByVehicleID(@RequestParam
	 * String paramsGrid) { PageVo page = new PageVo(); Map<String, Object>
	 * paramMap = FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam(); //
	 * paramMap.putAll(getUnitlevelParam(request));
	 * 
	 * List list =
	 * vehicleManagerservice.queryTerminalParamByVehicleID(paramMap);
	 * page.setRows(list); page.setPage((int) paramMap.get("page"));
	 * page.setTotal(vehicleManagerservice.queryCountTerminalParamByVehicleID(
	 * paramMap)); return page; }
	 * 
	 *//**
		 * 鏍规嵁杞﹁締ID鏌ユ壘澶氳竟褰俊鎭�
		 * 
		 * @param response
		 * @param paramsGrid
		 */
	/*
	 * @RequestMapping(value = "queryPolygonInfoByVehicleID.html")
	 * 
	 * @ResponseBody public PageVo queryPolygonInfoByVehicleID(@RequestParam
	 * String paramsGrid) { PageVo page = new PageVo(); Map<String, Object>
	 * paramMap = FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam(); //
	 * paramMap.putAll(getUnitlevelParam(request));
	 * 
	 * List list = vehicleManagerservice.queryPolygonInfoByVehicleID(paramMap);
	 * page.setRows(list); page.setPage((int) paramMap.get("page"));
	 * page.setTotal(vehicleManagerservice.queryCountPolygonInfoByVehicleID(
	 * paramMap)); return page; }
	 * 
	 *//**
		 * 鏍规嵁杞﹁締ID鏌ユ壘鍦嗗舰淇℃伅
		 * 
		 * @param response
		 * @param paramsGrid
		 */
	/*
	 * @RequestMapping(value = "queryRoundInfoByVehicleID.html")
	 * 
	 * @ResponseBody public PageVo queryRoundInfoByVehicleID(@RequestParam
	 * String paramsGrid) { PageVo page = new PageVo(); Map<String, Object>
	 * paramMap = FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam(); //
	 * paramMap.putAll(getUnitlevelParam(request)); List list =
	 * vehicleManagerservice.queryRoundInfoByVehicleID(paramMap);
	 * page.setRows(list); page.setPage((int) paramMap.get("page"));
	 * page.setTotal(vehicleManagerservice.queryCountRoundInfoByVehicleID(
	 * paramMap)); return page; }
	 * 
	 *//**
		 * 鏍规嵁杞﹁締ID鏌ユ壘鐭╁舰淇℃伅
		 * 
		 * @param response
		 * @param paramsGrid
		 *//*
		 * @RequestMapping(value = "queryRectangleInfoByVehicleID.html")
		 * 
		 * @ResponseBody public PageVo
		 * queryRectangleInfoByVehicleID(@RequestParam String paramsGrid) {
		 * PageVo page = new PageVo(); Map<String, Object> paramMap =
		 * FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam(); //
		 * paramMap.putAll(getUnitlevelParam(request)); List list =
		 * vehicleManagerservice.queryRectangleInfoByVehicleID(paramMap);
		 * page.setRows(list); page.setPage((int) paramMap.get("page"));
		 * page.setTotal(vehicleManagerservice.
		 * queryCountRectangleInfoByVehicleID(paramMap)); return page; }
		 * 
		 * @RequestMapping(value = "loadOilSignDetails.html")
		 * 
		 * @ResponseBody public PageVo loadOilSignDetails(@RequestParam String
		 * paramsGrid) { PageVo page = new PageVo(); Map<String, Object>
		 * paramMap = FlexiGridUtil.parseJSONParam(paramsGrid).getWhereParam();
		 * // paramMap.putAll(getUnitlevelParam(request)); List list =
		 * vehicleManagerservice.queryOilSignDetails(paramMap);
		 * page.setRows(list); page.setPage((int) paramMap.get("page"));
		 * page.setTotal(vehicleManagerservice.OilSignDetailsCount(paramMap));
		 * return page; }
		 * 
		 * @RequestMapping(value = "queryAllOilconfigure.html")
		 * 
		 * @ResponseBody public List queryAllOilconfigure() { List list =
		 * vehicleManagerservice.queryAllOilconfigure(); return list; }
		 */
	/**
	 * 楠岃瘉褰撳墠浼佷笟涓嬫槸鍚﹀凡缁忓瓨鍦ㄨ鑷紪鍙�
	 * 
	 * @param
	 * @param internalNumber
	 * @param WorkUnitId
	 * @return
	 *//*
		 * @RequestMapping(value = "checkIfExistNumber.html")
		 * 
		 * @ResponseBody public Map<String, Object>
		 * checkIfExistNumber(@RequestParam String internalNumber, String
		 * WorkUnitId, String id) { Map<String, Object> resultVo = new
		 * HashMap<>(); HashMap<String, Object> params = new HashMap<>();
		 * params.put("internalNumber", internalNumber);
		 * params.put("WorkUnitID", WorkUnitId); params.put("ID", id); int Count
		 * = vehicleManagerservice.checkIfExistNumber(params);
		 * 
		 * if (Count == 0) { resultVo.put("code",
		 * ResultContans.RETURN_OK_CODE.getCode()); } else {
		 * resultVo.put("code", ResultContans.RETURN_REPENT_CODE.getCode()); }
		 * return resultVo; }
		 */
	/*
	 * @SuppressWarnings("unchecked")
	 * 
	 * @RequestMapping(value = "queryLevelVehicleTree.html")
	 * 
	 * @ResponseBody public PageVo queryLevelVehicleTree(@RequestParam String
	 * VehicleID) { PageVo pageVo = new PageVo(); HashMap<String, Object>
	 * paramMap = getReqParameters();
	 * 
	 * List<SubPlatform> subPlatForm = (List<SubPlatform>)
	 * getSession().getAttribute(Constant.USER_OP_SUB); for(SubPlatform
	 * sub:subPlatForm){
	 * if(paramMap.get("unitId")==null&&.equals(sub.getDb_pool())&&sub.
	 * getRole_work_unit_id()!=-1){ Unit u =unitService.queryUnitById(
	 * sub.getRole_work_unit_id()); if(u!=null&&u.getFullId()!=null){
	 * paramMap.put("authWorkUnitFullId", u.getFullId()); } } };
	 * 
	 * String registrationNO = paramMap.get("q") != null ?
	 * paramMap.get("q").toString() : ""; paramMap.put("RegistrationNO",
	 * registrationNO); // 濡傛灉绛変簬绌猴紝鎼滅储褰撳墠杞﹁締 if (registrationNO == "" &&
	 * VehicleID != "") { paramMap.put("ID", VehicleID); } List<HashMap<String,
	 * Object>> list = vehicleManagerservice.queryLevelVehicleTree(paramMap);
	 * pageVo.setRows(list); return pageVo; }
	 * 
	 * // 鏌ヨ鎵�鏈夌粓绔被鍨嬫爲
	 * 
	 * @RequestMapping(value = "loadAllTerminalKindTreesOfCommand.html")
	 * 
	 * @ResponseBody public List<HashMap<String, Object>>
	 * loadAllTerminalKindTreesOfCommand() { List<HashMap<String, Object>>
	 * kindList = new ArrayList<>(); try { HashMap<String, Object> temp1 = new
	 * HashMap<>(); temp1.put("id", 0); temp1.put("name", "鍏ㄩ儴");
	 * List<TerminalKind> rows = terminalService.loadAllTerminalKindTrees();
	 * kindList.add(temp1); for (TerminalKind terminalKind : rows) {
	 * HashMap<String, Object> temp = new HashMap<>(); temp.put("id",
	 * terminalKind.getId()); temp.put("name", terminalKind.getName());
	 * kindList.add(temp); } return kindList; } catch (Exception e) {
	 * logger.error("", e); return new ArrayList(); } }
	 */
	// 获取运政车辆信息
	@RequestMapping(value = "getVeicleInfo.html")
	@ResponseBody
	public Map<String, Object> getVeicleInfo(@RequestParam String cphm, String cpys,String yszhm,String ziparea) {
		Map<String, Object> result = new HashMap<>();
		try {
			vehicleManagerservice.synVeicleInfo(cphm,cpys,yszhm,ziparea);
			result.put("code", Constant.RETURN_OK_CODE);
			result.put("msg", "操作成功!");
		} catch (Exception e) {
			e.printStackTrace();
			result.put("code", Constant.RETURN_ERROR_CODE);
			result.put("msg", "操作失败!");
		}
		return result;
	}
	
	// 同步运政车辆信息
	@RequestMapping(value = "synVeicleInfo.html")
	@ResponseBody
	public Map<String, Object> synVeicleInfo(String[] vehicleIds) {
		
		Map<String, Object> result = new HashMap<>();
		try {
			for (int i = 0; i < vehicleIds.length; i++) {
				Map<String,Object> mo = vehicleManagerservice.queryVehicleVoById(Integer.valueOf(vehicleIds[i]));
				if(mo.get("vehicleNo")!=null&&mo.get("vehicleColor")!=null&&mo.get("transportPermitsion")!=null&&mo.get("code")!=null){
					boolean flag = vehicleManagerservice.synVeicleInfo(mo.get("vehicleNo").toString(),mo.get("vehicleColor").toString(),mo.get("transportPermitsion").toString(),mo.get("code").toString());
					if(flag){
						result.put("code", Constant.RETURN_OK_CODE);
						result.put("msg", "操作成功!");
					}else{
						result.put("code", 1);
						result.put("msg", "运政无数据!");
					}
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			result.put("code", Constant.RETURN_ERROR_CODE);
			result.put("msg", "操作失败!");
		}
		return result;
	}
	
	// 导出车辆模板
	@RequestMapping(value = "importVehilceExcelTemplate.html", method = RequestMethod.POST)
	@ResponseBody
	public void importVehilceExcelTemplate(HttpServletResponse response) throws Exception {
		OutputStream outputStream = null;
		try{
			outputStream = response.getOutputStream();// 取得输出流
			response.reset();// 清空输出流
			//String filename = new String(fileName.getBytes("UTF-8"),"ISO8859-1");
			response.setHeader("Content-Disposition", "attachment; filename=" +  java.net.URLEncoder.encode("车辆信息模板", "UTF-8")+ ".xls"); 
			response.setContentType("application/msexcel");// 定义输出类型
			response.setCharacterEncoding("utf-8");
			Workbook wb = new HSSFWorkbook();
	        Sheet sheet = wb.createSheet("车辆信息导入模板");
	        // 第一行
	        Row row = sheet.createRow(0);
	        
	        CellStyle style = CatalogExcelUtil.getHeadStyle(wb);
	        CatalogExcelUtil.initCell(row.createCell(0), style, "车牌号码(必填)");
	        sheet.setColumnWidth(0, 4000);
	        CatalogExcelUtil.initCell(row.createCell(1), style, "车牌颜色(必填)");
	        sheet.setColumnWidth(1, 4000);
	        CatalogExcelUtil.initCell(row.createCell(2), style, "所属行业(必填)");
	        sheet.setColumnWidth(2, 4000);
	        CatalogExcelUtil.initCell(row.createCell(3), style, "所属业户(必填)");
	        sheet.setColumnWidth(3, 4000);
	        CatalogExcelUtil.initCell(row.createCell(4), style, "营运证号(必填)");
	        sheet.setColumnWidth(4, 4000);
	        CatalogExcelUtil.initCell(row.createCell(5), style, "车辆类型");
	        // 设置车牌颜色
	        //List<Map<String,Object>> colors = vehicleManagerservice.getVehicleColor();
	        String[] colorSelectList = new String[]{"蓝色","黄色","黑色","白色","其他"};
//	        for(int i=0;i<colors.size();i++){
//	        	colorSelectList[i] = colors.get(i).get("name").toString()+"-"+colors.get(i).get("code");
//	        }
	        // 第3列的第1行到第21行单元格部门下拉 ，可替换为从数据库的部门表数据，
	        // hidden_depart 为隐藏的sheet的别名，1为这个sheet的索引 ，考虑到有多个列绑定下拉列表
	        wb = dropDownList2003(wb, sheet, colorSelectList, 1, 500, 1, 1, "hidden_color", 1);
	        // 设置行业
	        List<Map<String,Object>> trades = vehicleManagerservice.getVehicleTrades();
	        String[] tradeSelectList = new String[trades.size()];
	        for(int i=0;i<trades.size();i++){
	        	tradeSelectList[i] = trades.get(i).get("name").toString();
	        }
	        wb = dropDownList2003(wb, sheet, tradeSelectList, 1, 500, 2, 2, "hidden_trade", 2);
	        //设置业户
	        List<Map<String,Object>> owners = vehicleManagerservice.getVehicleOwner();
	        String[] ownerSelectList = new String[owners.size()];
	        for(int i=0;i<owners.size();i++){
	        	ownerSelectList[i] = owners.get(i).get("name").toString();
	        }
	        wb = dropDownList2003(wb, sheet, ownerSelectList, 1, 500, 3, 3, "hidden_owner", 3);
	        
	        //设置车辆类型
	        List<Map<String,Object>> carType = vehicleManagerservice.getVehicleCarType();
	        String[] carTypeSelectList = new String[carType.size()];
	        for(int i=0;i<carType.size();i++){
	        	carTypeSelectList[i] = carType.get(i).get("vehicle_type_name").toString();
	        }
	        wb = dropDownList2003(wb, sheet, carTypeSelectList, 1, 500, 5, 5, "hidden_cartype", 4);
	        wb.write(outputStream);
		}catch(Exception e){
			e.printStackTrace();
			
		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
//		return result;
	}
	
	/**
     * @param wb HSSFWorkbook对象
     * @param realSheet 需要操作的sheet对象
     * @param datas 下拉的列表数据
     * @param startRow 开始行
     * @param endRow 结束行
     * @param startCol 开始列
     * @param endCol 结束列
     * @param hiddenSheetName 隐藏的sheet名
     * @param hiddenSheetIndex 隐藏的sheet索引
     * @return
     * @throws Exception
     */
    public HSSFWorkbook dropDownList2003(Workbook wb, Sheet realSheet, String[] datas, int startRow, int endRow,int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex){
        HSSFWorkbook workbook = (HSSFWorkbook) wb;
        // 创建一个数据源sheet
        HSSFSheet hidden = workbook.createSheet(hiddenSheetName);
        // 数据源sheet页不显示
        workbook.setSheetHidden(hiddenSheetIndex, true);
        // 将下拉列表的数据放在数据源sheet上
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = 0, length = datas.length; i < length; i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas[i]);
        }
        //2016-12-15更新，遇到问题：生成的excel下拉框还是可以手动编辑，不满足
        //HSSFName namedCell = workbook.createName();
        //namedCell.setNameName(hiddenSheetName);
        // A1 到 Adatas.length 表示第一列的第一行到datas.length行，需要与前一步生成的隐藏的数据源sheet数据位置对应
        //namedCell.setRefersToFormula(hiddenSheetName + "!$A$1:$A" + datas.length);
        // 指定下拉数据时，给定目标数据范围 hiddenSheetName!$A$1:$A5   隐藏sheet的A1到A5格的数据
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$A$1:$A" + datas.length);
        CellRangeAddressList addressList = null;
        HSSFDataValidation validation = null;
        row = null;
        cell = null;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (HSSFRow) realSheet.createRow(i);
            cell = row.createCell(startCol);
            cell.setCellStyle(style);
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = new HSSFDataValidation(addressList, constraint);
            realSheet.addValidationData(validation);
        }
        return workbook;
    }
    
    @RequestMapping(value = "importVehicle.html")
	@ResponseBody
	public Map<String, Object> importVehicle(HttpServletResponse response,MultipartHttpServletRequest multipartRequest, String fileUploadId) {
		Map<String, Object> result = new HashMap<>();
		try {
			MultipartFile file = multipartRequest.getFile(fileUploadId); // 根据前台的name名称得到上传的文件
			InputStream iso = file.getInputStream();
			HSSFWorkbook wb = new HSSFWorkbook(iso);
			HSSFSheet sheet = wb.getSheetAt(0);
			StringBuilder errorList = new StringBuilder();
			List<Map<String,Object>> cmpList = new ArrayList<>();
			//所有的车牌颜色
//			HashMap<String, Integer> vehicleColor = getVehicleColor();
			//所有行业类型
			HashMap<String, Integer> vehicleTrade = getVehicleTrade();
			//所有车辆类型
			HashMap<String, Integer> vehicleType = getVehicleType();
			//所有车辆业户
			HashMap<String, Integer> vehicleOwner = getVehicleOwner();

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {//从第二行开始
				HSSFRow row = sheet.getRow(i);
				if (null == row) {
					continue;
				}
				String str0 = getCellValue(row.getCell(0));//cell0.getStringCellValue();//车牌号码
				String str1 = getCellValue(row.getCell(1));//cell1.getStringCellValue();//车牌颜色
				String str2 = getCellValue(row.getCell(2));//cell2.getStringCellValue();//所属行业
				String str3 = getCellValue(row.getCell(3));//cell4.getStringCellValue();//所属业户
				String str4 = getCellValue(row.getCell(4));//cell5.getStringCellValue();//营运证号
				String str5 = getCellValue(row.getCell(5));//cell7.getStringCellValue();//车辆类型
				if ("".equals(str1)&&"".equals(str0)) {
					break;
				}
//				if(str0.matches("^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领A-Z]{1}[A-Z]{1}[A-Z0-9]{4}[A-Z0-9挂学警港澳]{1}$")){
//					errorList.append("第" + (i + 1) + "行数据车牌号非正常,");
//					continue;
//				}
				if(str0.length()!=7){
					errorList.append("第" + (i + 1) + "行数据车牌号非七位数,");
					continue;
				}
				logger.info("第" + (i + 1) + "行数据" + str0 + "---" + str1 + "---" + str2 + "---" + str3 + "---" + str4 + "---" + str5 );
				HashMap<String, Object> param = new HashMap<String, Object>();
				param.put("vehicleNo", str0.toString());
				if ("".equals(str0) || str0 == null) {
					logger.info("车牌号不存在{}", str0);
					errorList.append("第" + (i + 1) + "行数据车牌号为空,");
					continue;
				}
				if ("".equals(str1) || str1 == null) {
					logger.info("车牌颜色为空{}", str0);
					errorList.append("第" + (i + 1) + "行数据车牌颜色为空,");
					continue;
				}
				if("蓝色".equals(str1)){
					param.put("colorId", 1);
				}else if("黄色".equals(str1)){
					param.put("colorId", 2);
				}else if("黑色".equals(str1)){
					param.put("colorId", 3);
				}else if("白色".equals(str1)){
					param.put("colorId", 4);
				}else if("其他".equals(str1)){
					param.put("colorId", 9);
				}
//				param.put("colorId", vehicleColor.get(str1));
				int count = vehicleManagerservice.checkIfExistNumber(param);
				if (count>0) {
					logger.info("已存在同样车牌号和车牌颜色{}", str1);
					errorList.append("第" + (i + 1) + "行数据已存在同样车牌号和车牌颜色,");
					continue;
				}
//				if (vehicleColor.get(str1) == null || "".equals(vehicleColor.get(str1))) {
//					logger.info("车牌颜色不存在{}", str1);
//					errorList.append("第" + (i + 1) + "行数据车牌颜色不存在,");
//					continue;
//				}
				if ("".equals(str2) || str2 == null) {
					logger.info("所属行业为空{}", str2);
					errorList.append("第" + (i + 1) + "行数据所属行业为空,");
					continue;
				}
				if (vehicleTrade.get(str2) == null || "".equals(vehicleTrade.get(str2))) {
					logger.info("所属行业不存在{}", str2);
					errorList.append("第" + (i + 1) + "行数据所属行业不存在,");
					continue;
				}
				param.put("tradeId", vehicleTrade.get(str2));
				if ("".equals(str3) || str3 == null) {
					logger.info("所属业户为空{}", str3);
					errorList.append("第" + (i + 1) + "行数据所属业户为空,");
					continue;
				}
				if (vehicleOwner.get(str3) == null || "".equals(vehicleOwner.get(str3))) {
					logger.info("所属业户不存在{}", str3);
					errorList.append("第" + (i + 1) + "行数据所属业户不存在,");
					continue;
				}
				param.put("enterpriseId", vehicleOwner.get(str3));
				
				if ("".equals(str4) || str4 == null) {
					errorList.append("第" + (i + 1) + "行数据经营许可证为空,");
					continue;
				}
				if(!str4.matches("^[0-9_]+$")){//正则表达式 【匹配是否为数字
					errorList.append("第" + (i + 1) + "行数据经营许可证非数字,");
					continue;
				}
				param.put("transportPermitsion", str4);
				int check = vehicleManagerservice.existPermitsion(param);
				if (check>0) {
					logger.info("已存在同样营运证号{}", str4);
					errorList.append("第" + (i + 1) + "行数据已存在同样营运证号,");
					continue;
				}
				if ("".equals(str5) || str5 == null) {
					logger.info("车辆类型为空{}", str5);
					errorList.append("第" + (i + 1) + "行数据车辆类型为空,");
					continue;
				}
				if (vehicleType.get(str5) == null || "".equals(vehicleType.get(str5))) {
					logger.info("车辆类型不存在{}", str5);
					errorList.append("第" + (i + 1) + "行数据车辆类型不存在,");
					continue;
				}
				param.put("kindId", vehicleType.get(str5));
				param.put("province", "桂");
				param.put("city", "南");
				vehicleManagerservice.saveVehicleImport(param);
				cmpList.add(param);
			}
			String str = "";
			if (errorList.length() > 0) {
				str = errorList.deleteCharAt(errorList.length() - 1).toString();
			}
			logger.error("共导入车辆数: " + cmpList.size());
			result.put("code", Constant.RETURN_OK_CODE);
			result.put("data", str);
		
		} catch (Exception e) {
			System.err.println(e);
			result.put("data", "数据导入表格格式不对、请更改格式之后进行导入！！！");
			logger.error("", e);
		}
		return result;
	}

	private HashMap<String, Integer> getVehicleOwner() {
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		List<Map<String,Object>> owners = vehicleManagerservice.getVehicleOwner();
		for(int i=0;i<owners.size();i++){
			map.put(owners.get(i).get("name").toString(), Integer.valueOf(owners.get(i).get("id").toString()));
		}
		return map;
	}

	private HashMap<String, Integer> getVehicleType() {
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		List<Map<String,Object>> types = vehicleManagerservice.getVehicleCarType();
		for(int i=0;i<types.size();i++){
			map.put(types.get(i).get("vehicle_type_name").toString(), Integer.valueOf(types.get(i).get("id").toString()));
		}
		return map;
	}

	private HashMap<String, Integer> getVehicleTrade() {
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		List<Map<String,Object>> trades = vehicleManagerservice.getVehicleTrades();
		for(int i=0;i<trades.size();i++){
			map.put(trades.get(i).get("name").toString(), Integer.valueOf(trades.get(i).get("id").toString()));
		}
		return map;
	}

	private HashMap<String, Integer> getVehicleColor() {
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		List<Map<String,Object>> colors = vehicleManagerservice.getVehicleColor();
		for(int i=0;i<colors.size();i++){
			map.put(colors.get(i).get("name")+"-"+colors.get(i).get("code"), Integer.valueOf(colors.get(i).get("id").toString()));
		}
		return map;
	}
}
